package cn.ichiva.luckysheet.utils;

import cn.ichiva.luckysheet.enenty.Ct;
import cn.ichiva.luckysheet.enenty.V;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jayway.jsonpath.Configuration;
import com.jayway.jsonpath.JsonPath;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.SQLOutput;
import java.util.*;
import java.util.stream.Collectors;

public class groupTest {
    public static void main(String[] args) throws SQLException, ClassNotFoundException{
        int xlast=0;
        int ylast=0;
        //获取数据

        String str =  HttpRequest.sendGet("http://api.jeecg.com/mock/26/subtotal",null);
        JSONObject jsonObjectStr  = JSONObject.parseObject(str);
        List<Map<String,Object>> list = (List<Map<String, Object>>) jsonObjectStr.get("data");

        //y轴最后一个格子的坐标为 表头所占+数据填充个数
        ylast=list.size()+2;
        //需要替换内容的
        List<JSONObject> listdaichuli= new ArrayList<>();
        List<JSONObject> groupList = new ArrayList<>();
        String json ="[{\"name\":\"Cell\",\"index\":\"sheet_01\",\"order\":0,\"status\":1,\"celldata\":[{\"r\":0,\"c\":0,\"v\":{\"v\":\"人员表1\",\"m\":\"人员表1\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"mc\":{\"r\":0,\"c\":0,\"rs\":1,\"cs\":4},\"ht\":\"0\"}},{\"r\":0,\"c\":1,\"v\":{\"mc\":{\"r\":0,\"c\":0},\"ht\":\"0\"}},{\"r\":0,\"c\":2,\"v\":{\"mc\":{\"r\":0,\"c\":0},\"ht\":\"0\"}},{\"r\":0,\"c\":3,\"v\":{\"mc\":{\"r\":0,\"c\":0},\"ht\":\"0\"}},{\"r\":1,\"c\":0,\"v\":{\"m\":\"姓名\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"姓名\"}},{\"r\":1,\"c\":1,\"v\":{\"m\":\"年级\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"年级\"}},{\"r\":1,\"c\":2,\"v\":{\"m\":\"分数\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"分数\"}},{\"r\":1,\"c\":3,\"v\":{\"m\":\"性别\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"性别\"}},{\"r\":2,\"c\":0,\"v\":{\"m\":\"select(name)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(name)\"}},{\"r\":2,\"c\":1,\"v\":{\"m\":\"select(grade)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(grade)\"}},{\"r\":2,\"c\":2,\"v\":{\"m\":\"select(fraction)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(fraction)\"}},{\"r\":2,\"c\":3,\"v\":{\"m\":\"select(gender)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(gender)\"}},{\"r\":2,\"c\":4,\"v\":{\"v\":\"2\",\"ct\":{\"fa\":\"General\",\"t\":\"n\"},\"m\":\"2\"}}],\"jfgird_select_save\":[],\"luckysheet_select_save\":[{\"row\":[3,3],\"column\":[4,4],\"row_focus\":3,\"column_focus\":4,\"moveXY\":{\"x\":3,\"y\":4},\"left\":296,\"width\":73,\"top\":60,\"height\":19,\"left_move\":296,\"width_move\":73,\"top_move\":60,\"height_move\":19}],\"config\":{\"merge\":{\"0_0\":{\"r\":0,\"c\":0,\"rs\":1,\"cs\":4}},\"borderInfo\":[{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"color\":\"#000\",\"style\":\"1\",\"range\":[{\"left\":0,\"width\":295,\"top\":-1,\"height\":20,\"left_move\":0,\"width_move\":295,\"top_move\":0,\"height_move\":59,\"row\":[0,2],\"column\":[0,3],\"row_focus\":0,\"column_focus\":0}]}]},\"visibledatarow\":[20,40,60,80,100,120,140,160,180,200,220,240,260,280,300,320,340,360,380,400,420,440,460,480,500,520,540,560,580,600,620,640,660,680,700,720,740,760,780,800,820,840,860,880,900,920,940,960,980,1000,1020,1040,1060,1080,1100,1120,1140,1160,1180,1200,1220,1240,1260,1280,1300,1320,1340,1360,1380,1400,1420,1440,1460,1480,1500,1520,1540,1560,1580,1600,1620,1640,1660,1680],\"visibledatacolumn\":[74,148,222,296,370,444,518,592,666,740,814,888,962,1036,1110,1184,1258,1332,1406,1480,1554,1628,1702,1776,1850,1924,1998,2072,2146,2220,2294,2368,2442,2516,2590,2664,2738,2812,2886,2960,3034,3108,3182,3256,3330,3404,3478,3552,3626,3700,3774,3848,3922,3996,4070,4144,4218,4292,4366,4440],\"ch_width\":4560,\"rh_height\":1760,\"luckysheet_selection_range\":[],\"zoomRatio\":1},{\"name\":\"Data\",\"index\":\"sheet_02\",\"order\":1,\"status\":0,\"celldata\":[]},{\"name\":\"Picture\",\"index\":\"sheet_03\",\"order\":2,\"status\":0,\"celldata\":[]}]";
        String json2 ="[{\"name\":\"Cell\",\"index\":\"sheet_01\",\"order\":0,\"status\":1,\"celldata\":[{\"r\":0,\"c\":0,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"ht\":\"0\",\"m\":\"区域\",\"v\":\"区域\",\"mc\":{\"r\":0,\"c\":0,\"rs\":2,\"cs\":1}}},{\"r\":0,\"c\":1,\"v\":{\"ht\":\"0\",\"m\":\"省份\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"省份\",\"mc\":{\"r\":0,\"c\":1,\"rs\":2,\"cs\":1}}},{\"r\":0,\"c\":3,\"v\":{\"mc\":{\"r\":0,\"c\":2}}},{\"r\":0,\"c\":4,\"v\":{\"mc\":{\"r\":0,\"c\":2}}},{\"r\":1,\"c\":0,\"v\":{\"mc\":{\"r\":0,\"c\":0}}},{\"r\":1,\"c\":1,\"v\":{\"mc\":{\"r\":0,\"c\":1}}},{\"r\":1,\"c\":2,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"m\":\"单位\",\"v\":\"单位\"}},{\"r\":1,\"c\":3,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"m\":\"金额\",\"v\":\"金额\"}},{\"r\":1,\"c\":4,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"m\":\"小计\",\"v\":\"小计\"}},{\"r\":2,\"c\":0,\"v\":{\"m\":\"#groupt(areaname)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#groupt(areaname)\"}},{\"r\":2,\"c\":1,\"v\":{\"m\":\"#group(dept)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#group(dept)\"}},{\"r\":2,\"c\":2,\"v\":{\"m\":\"#select(price)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#select(price)\"}},{\"r\":2,\"c\":3,\"v\":{\"m\":\"#select(amount)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#select(amount)\"}},{\"r\":2,\"c\":4,\"v\":{\"m\":\"#select(price)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#select(price)\"}}],\"jfgird_select_save\":[],\"luckysheet_select_save\":[{\"left\":436,\"width\":135,\"top\":120,\"height\":19,\"left_move\":436,\"width_move\":135,\"top_move\":120,\"height_move\":19,\"row\":[6,6],\"column\":[3,3],\"row_focus\":6,\"column_focus\":3}],\"config\":{\"merge\":{\"0_0\":{\"r\":0,\"c\":0,\"rs\":2,\"cs\":1},\"0_1\":{\"r\":0,\"c\":1,\"rs\":2,\"cs\":1},\"0_2\":{\"r\":0,\"c\":2,\"rs\":1,\"cs\":3}},\"borderInfo\":[{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]},{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]},{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]},{\"rangeType\":\"range\",\"borderType\":\"border-none\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]}],\"columnlen\":{\"0\":149,\"1\":126,\"2\":158,\"3\":135,\"4\":131,\"5\":141},\"customWidth\":{\"0\":1,\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1},\"rowlen\":{},\"rowhidden\":{}},\"visibledatarow\":[20,40,60,80,100,120,140,160,180,200,220,240,260,280,300,320,340,360,380,400,420,440,460,480,500,520,540,560,580,600,620,640,660,680,700,720,740,760,780,800,820,840,860,880,900,920,940,960,980,1000,1020,1040,1060,1080,1100,1120,1140,1160,1180,1200,1220,1240,1260,1280,1300,1320,1340,1360,1380,1400,1420,1440,1460,1480,1500,1520,1540,1560,1580,1600,1620,1640,1660,1680],\"visibledatacolumn\":[150,277,436,572,704,846,920,994,1068,1142,1216,1290,1364,1438,1512,1586,1660,1734,1808,1882,1956,2030,2104,2178,2252,2326,2400,2474,2548,2622,2696,2770,2844,2918,2992,3066,3140,3214,3288,3362,3436,3510,3584,3658,3732,3806,3880,3954,4028,4102,4176,4250,4324,4398,4472,4546,4620,4694,4768,4842],\"ch_width\":4962,\"rh_height\":1760,\"luckysheet_selection_range\":[],\"zoomRatio\":1,\"dynamicArray\":[],\"images\":{},\"scrollLeft\":0,\"scrollTop\":0,\"calcChain\":[],\"filter_select\":null,\"filter\":null,\"luckysheet_conditionformat_save\":[],\"luckysheet_alternateformat_save\":[],\"dataVerification\":{},\"hyperlink\":{}},{\"name\":\"Data\",\"index\":\"sheet_02\",\"order\":1,\"status\":0,\"celldata\":[]},{\"name\":\"Picture\",\"index\":\"sheet_03\",\"order\":2,\"status\":0,\"celldata\":[]}]";

        JSONArray jsonArray = (JSONArray) JSONObject.parse(json2);
        //如果只有一个sheet那就是get(0),有多个那就对应取下标
        JSONObject jsonObject = (JSONObject) jsonArray.get(0);
        JSONArray jsonObjectList = jsonObject.getJSONArray("celldata");
        for(int i=0;i<jsonObjectList.size();i++) {
            String v=jsonObjectList.getJSONObject(i).get("v").toString();
            JSONObject jsonObject1 =JSONObject.parseObject(v);
            if(ObjectUtils.isNotEmpty(jsonObject1.get("m"))){
                //获得所有的select函数
                if(jsonObject1.get("m").toString().lastIndexOf("select")!= -1  ){
                    JSONObject jsonn = new JSONObject();
                    V v1 = new V();
                    jsonn.put("r",jsonObjectList.getJSONObject(i).get("r"));
                    jsonn.put("c",jsonObjectList.getJSONObject(i).get("c"));
                    jsonn.put("v",jsonObject1);
                    v1.setM(jsonObject1.get("m").toString());
                    Ct ct = new Ct();
                    ct.setFa("General");
                    ct.setT("g");
                    v1.setCt(ct);
                    listdaichuli.add(jsonn);
                }
                /*获取所有的group分组函数*/
                if( jsonObject1.get("m").toString().lastIndexOf("group")!= -1){
                    JSONObject jsonn = new JSONObject();
                    V v1 = new V();
                    jsonn.put("r",jsonObjectList.getJSONObject(i).get("r"));
                    jsonn.put("c",jsonObjectList.getJSONObject(i).get("c"));
                    jsonn.put("v",jsonObject1);
                    v1.setM(jsonObject1.get("m").toString());
                    Ct ct = new Ct();
                    ct.setFa("General");
                    ct.setT("g");
                    v1.setCt(ct);
                    groupList.add(jsonn);
                }
            }
        }
        /*组装后的数据*/
        /*数据分组*/
        List<Map<String, Object>> dataList = zuzhuang(groupList, list);


        JSONArray jsonObjectListn = new JSONArray();
//        for (int i = 0; i < list.size(); i++) {
//            for (int j = 0; j < listdaichuli.size(); j++) {
//                String v=listdaichuli.get(j).get("v").toString();
//                JSONObject jsonObject1 =JSONObject.parseObject(v);
//                String sel=jsonObject1.get("m").toString();
//                String ghj=sel.substring(sel.indexOf("(")+1,sel.indexOf(")"));
//                JSONObject json1 = new JSONObject();
//                V v2 = new V();
//                json1.put("r",i+2);
//                json1.put("c",j);
//                json1.put("v",v2);
//                v2.setM(list.get(i).get(ghj).toString());
//                v2.setV(list.get(i).get(ghj).toString());
//                Ct ct = new Ct();
//                ct.setFa("General");
//                ct.setT("g");
//                v2.setCt(ct);
//                jsonObjectList.add(json1);
//            }
//        }

        JSONObject jsonObjectconfig = jsonObject.getJSONObject("config");
        String borderInfo=jsonObjectconfig.get("borderInfo").toString();
        JSONArray jsonObject1 =JSONArray.parseArray(borderInfo);
        String merge  =  jsonObjectconfig.get("merge").toString();
        JSONObject jsonMerge = JSONObject.parseObject(merge);
        JSONObject jsonObject11 = null;
        String key = "";
            //处理group
            for (int j = 0; j < groupList.size(); j++) {
                for (int i = 0; i < dataList.size(); i++) {
                    String v = groupList.get(j).get("v").toString();
                    Integer r = (Integer) groupList.get(j).get("r");
                    Integer c = (Integer) groupList.get(j).get("c");
                    JSONObject jsonObject2 = JSONObject.parseObject(v);
                    String sel = jsonObject2.get("m").toString();
                    String ghj = sel.substring(sel.indexOf("(") + 1, sel.indexOf(")"));
                    JSONObject json1 = new JSONObject();
                    V v2 = new V();
                    json1.put("r", i + r);
                    json1.put("c", c);
                    json1.put("v", v2);
                    v2.setM(dataList.get(i).get(ghj).toString());
                    v2.setV(dataList.get(i).get(ghj).toString());
                    Ct ct = new Ct();
                    ct.setFa("General");
                    ct.setT("g");
                    v2.setCt(ct);
                    jsonObjectList.add(json1);
                    if (i == 0){
                        jsonObject11 = new JSONObject();
                        key = r+"_"+c;
                        jsonObject11.put("r",r);
                        jsonObject11.put("c",c);
                        jsonObject11.put("rs",1);
                        jsonObject11.put("cs",1);
                    }
                    if(i!= 0){
                        String newValue = dataList.get(i).get(ghj).toString();
                        String oldValue = dataList.get(i-1).get(ghj).toString();
                        if(oldValue.equals(newValue)){
                            Integer integer = (Integer) jsonObject11.get("rs");
                            jsonObject11.put("rs",integer+1);
                        }
                        else{
                            jsonMerge.put(key,jsonObject11);
                            jsonObject11 = new JSONObject();
                            key = (r+i)+"_"+c;
                            jsonObject11.put("r",r);
                            jsonObject11.put("c",c);
                            jsonObject11.put("rs",1);
                            jsonObject11.put("cs",1);
                        }
                    }
                }
                jsonMerge.put(key,jsonObject11);
            }

        for (int i = 0; i < dataList.size(); i++) {
            /*处理select*/
            for (int j = 0; j < listdaichuli.size(); j++) {
                String v=listdaichuli.get(j).get("v").toString();
                Integer r = (Integer) listdaichuli.get(j).get("r");
                Integer c = (Integer) listdaichuli.get(j).get("c");
                JSONObject jsonObject2 =JSONObject.parseObject(v);
                String sel=jsonObject2.get("m").toString();
                String ghj=sel.substring(sel.indexOf("(")+1,sel.indexOf(")"));
                JSONObject json1 = new JSONObject();
                V v2 = new V();
                json1.put("r",i+r);
                json1.put("c",c);
                json1.put("v",v2);
                v2.setM(dataList.get(i).get(ghj).toString());
                v2.setV(dataList.get(i).get(ghj).toString());
                Ct ct = new Ct();
                ct.setFa("General");
                ct.setT("g");
                v2.setCt(ct);
                jsonObjectList.add(json1);
            }
        }




        //设置边框
        Configuration conf = Configuration.defaultConfiguration();
        String set = JsonPath.using(conf).parse(jsonObject).set("$['config']['borderInfo'][0]['range'][0]['column'][1]", 10).jsonString();
        String set1 = JsonPath.using(conf).parse(jsonObject).set("$['config']['merge']",jsonMerge).jsonString();


        JSONArray jsonArray1=(JSONArray) jsonObject1.getJSONObject(0).get("range");
        JSONArray jsonArray2=(JSONArray) jsonArray1.getJSONObject(0).get("column"); //x轴边框
        JSONArray jsonArray3=(JSONArray) jsonArray1.getJSONObject(0).get("row");//y轴边框

        Map<String, Object> jsonMap = JSONObject.toJavaObject(jsonArray1.getJSONObject(0), Map.class);
        Integer[] a = {0,xlast};
        String set123 = JsonPath.using(conf).parse(jsonObject).set("$['config']['borderInfo'][0]['range'][0]['column']", a).jsonString();
        jsonMap.put("column",a);
        Integer[] b = {0,ylast};
        jsonMap.put("row",b);
        String jsonty = JSON.toJSONString(jsonMap);//map转String
        JSONObject jsonObject2gg=jsonObject1.getJSONObject(0);
        jsonObject1.set(0,JSON.parseObject(jsonty));//String转json
        jsonObject.put("config",jsonObjectconfig);
//        JSONObject json1 = new JSONObject();
//        V v = new V();
//        json1.put("r","2");
//        json1.put("c","4");
//        json1.put("v",v);
//        v.setM("测试");
//        Ct ct = new Ct();
//        ct.setFa("General");
//        ct.setT("g");
//        v.setCt(ct);
//        jsonObjectList.add(json1);
        jsonObject.put("celldata",jsonObjectList);
        jsonArray.add(jsonObject);
        String g=jsonArray.toString();
        System.out.println(jsonArray);
    }



    public static List<Map<String,Object>>  zuzhuang(List<JSONObject> groupList, List<Map<String,Object>> list){
        List list1 = new ArrayList();
        int[] compareResultArr=new int[groupList.size()];
        for (int j = 0; j <groupList.size() ; j++) {
            String v=groupList.get(j).get("v").toString();
            JSONObject jsonObject1 =JSONObject.parseObject(v);
            String sel=jsonObject1.get("m").toString();
            String ghj=sel.substring(sel.indexOf("(")+1,sel.indexOf(")"));
            list1.add(ghj);
            compareResultArr[j] =  0;
        }
        /*数据进行分组*/
        Collections.sort(list, new Comparator<Map<String, Object>>() {
            @Override
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                // 进行判断
                for(int i=0;i<list1.size();i++) {
                    Object v1=o1.get(list1.get(i));
                    Object v2=o2.get(list1.get(i));
                    if (v1 instanceof String) {
                        compareResultArr[i]=v1.toString().compareTo(v2.toString());
                    }
                    else {
                        compareResultArr[i]=new BigDecimal(v1.toString()).compareTo(new BigDecimal(v2.toString()));
                    }

                }

                for(int i=0;i<list1.size();i++) {
                    if (compareResultArr[i]!=0) {
                        return compareResultArr[i];
                    }
                }
                //如果都是一样，则直接返回0
                return 0;
            }
        });
        return list;
    }


}
